Re: [SQL] combining columns in select

At 07:04 +0200 on 10/10/1999, Doug Thistlethwaite wrote:


> What I was trying to do is develop a java application using the ODBC and then
> move it to postgres & a linux database when I had everything working.
> Unfortunately, I have discovered that there are many differences in the
> "standard SQL" that each driver uses.
>
> I could not find any ODBC drivers that us local data that allow the "||"
> concatenation symbol.  Most of them use the "+" symbol.  I have found the
>same
> problem with "Memo" vs "text" fields as well.  The ODBC driver for dbase (the
> one which seems to work best in my case) also uses the # symbol for dates
> while postgres likes the ' symbol.
>
> I guess I have to live with this and write separate query statements for each
> implementation.

If you want to write a  cross-platform JDBC application, you must avoid
non-standard SQL. The SQL92 standard operator for string concatenation is
just a single pipe char |. So neither "+" nor "||" are standard. In this
case, I don't think either of the databases supports the actual standard.
You can probably overcome this difficulty by setting a variable to either
"||" or "+", according to the type of driver you get, and build the SQL
queries using this variable.

As for the other things, you really have to conform to the standard.
Neither Memo nor Text are standard types. If you want standard, stick to
CHARACTER VARYING. As for dates, there are escape sequences in JDBC which
are supposed to convert dates from a common format to the format acceptable
to your particular database. Use these, and you are "standard".

Herouth

--
Herouth Maoz, Internet developer.
Open University of Israel - Telem project
http://telem.openu.ac.il/~herutma